import java.io.*;
import java.nio.file.*;
import java.util.regex.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 将JSON格式的面试题转换为SQL语句的工具类（简化版，不依赖外部库）
 */
public class InterviewQuestionToSQLSimple {

    // JSON文件目录
    private static final String JSON_DIR = "interview_questions";
    // SQL输出目录
    private static final String SQL_OUTPUT_DIR = "generated_sql";
    
    /**
     * 主方法，执行转换过程
     */
    public static void main(String[] args) {
        try {
            convertJsonToSQL();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将JSON文件转换为SQL语句
     */
    public static void convertJsonToSQL() throws Exception {
        // 创建输出目录
        Path outputPath = Paths.get(SQL_OUTPUT_DIR);
        if (!Files.exists(outputPath)) {
            Files.createDirectory(outputPath);
        }

        // 获取所有JSON文件
        File jsonDir = new File(JSON_DIR);
        File[] jsonFiles = jsonDir.listFiles((dir, name) -> name.endsWith(".json"));
        
        if (jsonFiles == null || jsonFiles.length == 0) {
            System.out.println("未找到JSON文件");
            return;
        }

        // 创建总的SQL文件
        File allSqlFile = new File(SQL_OUTPUT_DIR + "/all_interview_questions.sql");
        try (BufferedWriter allWriter = new BufferedWriter(new FileWriter(allSqlFile))) {
            // 写入SQL文件头部信息
            allWriter.write("-- 面试问题SQL导入文件\n");
            allWriter.write("-- 自动生成于: " + new java.util.Date() + "\n\n");
            
            // 处理每个JSON文件
            for (File jsonFile : jsonFiles) {
                processJsonFile(jsonFile, allWriter);
            }
        }
        
        System.out.println("所有SQL文件已生成，保存在 " + SQL_OUTPUT_DIR + " 目录中");
    }

    /**
     * 处理单个JSON文件
     */
    private static void processJsonFile(File jsonFile, BufferedWriter allWriter) throws Exception {
        System.out.println("处理文件: " + jsonFile.getName());
        
        // 从文件名提取分类ID (cid)
        String fileName = jsonFile.getName();
        int categoryId = Integer.parseInt(fileName.replaceAll("question_list_cid_(\\d+)\\.json", "$1"));
        
        // 读取JSON文件内容
        String content = new String(Files.readAllBytes(jsonFile.toPath()));
        
        // 提取问题列表部分
        Pattern questionListPattern = Pattern.compile("\"questionList\"\\s*:\\s*\\[(.*?)\\]", Pattern.DOTALL);
        Matcher questionListMatcher = questionListPattern.matcher(content);
        
        if (questionListMatcher.find()) {
            String questionListStr = questionListMatcher.group(1);
            
            // 为每个分类创建单独的SQL文件
            String sqlFileName = SQL_OUTPUT_DIR + "/" + fileName.replace(".json", ".sql");
            try (BufferedWriter categoryWriter = new BufferedWriter(new FileWriter(sqlFileName))) {
                // 写入文件头部信息
                categoryWriter.write("-- " + fileName + " 的面试问题SQL导入文件\n");
                categoryWriter.write("-- 分类ID: " + categoryId + "\n\n");
                
                // 写入分类ID设置语句
                categoryWriter.write("-- 设置分类ID\n");
                categoryWriter.write("SET @category_id = " + (1000 + categoryId - 1) + ";\n\n");
                
                // 写入插入语句头部
                categoryWriter.write("INSERT INTO interview_question (tenant_id, content, type, difficulty, category_id, answer, analysis, status, del_flag, create_dept, create_by, create_time, update_by, update_time, remark) VALUES\n");
                allWriter.write("-- " + fileName + " 的面试问题\n");
                allWriter.write("SET @category_id = " + (1000 + categoryId - 1) + ";\n");
                allWriter.write("INSERT INTO interview_question (tenant_id, content, type, difficulty, category_id, answer, analysis, status, del_flag, create_dept, create_by, create_time, update_by, update_time, remark) VALUES\n");
                
                // 提取所有问题
                List<String> valuesList = extractQuestions(questionListStr);
                
                // 写入所有值
                for (int i = 0; i < valuesList.size(); i++) {
                    String value = valuesList.get(i);
                    String suffix = (i == valuesList.size() - 1) ? ";\n\n" : ",\n";
                    categoryWriter.write(value + suffix);
                    allWriter.write(value + suffix);
                }
                
                System.out.println("已生成 " + sqlFileName);
            }
        }
    }

    /**
     * 从问题列表字符串中提取各个问题
     */
    private static List<String> extractQuestions(String questionListStr) {
        List<String> questions = new ArrayList<>();
        
        // 匹配每个问题对象
        Pattern questionPattern = Pattern.compile("\\{[^\\{\\}]*?\"id\"\\s*:\\s*\\d+[^\\{\\}]*?\\}", Pattern.DOTALL);
        Matcher questionMatcher = questionPattern.matcher(questionListStr);
        
        while (questionMatcher.find()) {
            String questionStr = questionMatcher.group();
            String sqlValue = createSqlValue(questionStr);
            if (sqlValue != null) {
                questions.add(sqlValue);
            }
        }
        
        return questions;
    }

    /**
     * 从问题字符串创建SQL值
     */
    private static String createSqlValue(String questionStr) {
        // 提取问题内容
        String content = extractField(questionStr, "quContent");
        if (content == null) return null;
        
        // 提取分析内容
        String analysis = extractField(questionStr, "analysisMd");
        if (analysis == null) analysis = "";
        
        // 转义特殊字符
        content = escapeSql(content);
        analysis = escapeSql(analysis);
        
        // 默认值
        String tenantId = "000000";
        String type = "简答题";
        String difficulty = "中等";
        String answer = "";
        String status = "0";
        String delFlag = "0";
        String createDept = "103";
        String createBy = "1";
        String updateTime = "NOW()";
        String updateBy = "1";
        String remark = "NULL";
        
        // 构建SQL值字符串
        return String.format("('%s', '%s', '%s', '%s', @category_id, '%s', '%s', '%s', '%s', %s, %s, NOW(), %s, %s, %s)", 
                tenantId, content, type, difficulty, answer, analysis, status, delFlag, 
                createDept, createBy, updateBy, updateTime, remark);
    }

    /**
     * 从JSON对象字符串中提取指定字段的值
     */
    private static String extractField(String jsonObject, String fieldName) {
        Pattern pattern = Pattern.compile("\"" + fieldName + "\"\\s*:\\s*\"((?:[^\"\\\\]|\\\\.)*)\"");
        Matcher matcher = pattern.matcher(jsonObject);
        
        if (matcher.find()) {
            String value = matcher.group(1);
            // 处理转义字符
            value = value.replace("\\\"", "\"")
                         .replace("\\n", "\n")
                         .replace("\\r", "\r")
                         .replace("\\t", "\t");
            return value;
        }
        
        return null;
    }

    /**
     * 转义SQL字符串中的特殊字符
     */
    private static String escapeSql(String str) {
        if (str == null) {
            return "";
        }
        return str.replace("'", "\\'")
                 .replace("\\", "\\\\")
                 .trim();
    }
}